﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;

namespace Kit
{
    public class MySqlKit
    {
        /* MySQL寫法
        private const string kMySqlServer = "192.168.52.80";
        private const int kMySqlPort = 3309;
        private const string kMySqlUserId = "ccpsipuser";
        private static byte[] kMySqlKey = { 0x53, 0x59, 0x33, 0x43, 0x36, 0x38, 0x64, 0x33,
                                        0x78, 0x59, 0x57, 0x34, 0x61, 0x4A };
        private const string kMySqlDatabase = "ccpsip";


        /// <summary>
        /// 取得 CCPSIP 的 MySQL 連線字串
        /// </summary>
        /// <returns>MySQL 連線字串</returns>
        private static string getCcpsipConnStr()
        {
            string connStrMySql = String.Format(
              "server={0};port={1};database={2};uid={3};pwd={4};charset=utf8mb4;pooling=true",
              kMySqlServer, kMySqlPort, kMySqlDatabase, kMySqlUserId,
              System.Text.Encoding.UTF8.GetString(kMySqlKey));
            return connStrMySql;
        }
        */

        /// <summary>取得Oracle連線字串</summary>
        private static string GetConnStr()
        {
            return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.52.195)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SID=UNIDB)));uid=ISENDER;pwd=aKhJJFA2sWfEsQz0Zm5iwQi9gjiVND";
        }

        /// <summary>
        /// 取得 CCPSIP User 的 Full Name
        /// </summary>
        /// <param name="SIP_Num">SIP分機號碼</param>
        /// <returns>有此 User 則回傳 Full Name，否則 null</returns>
        public static string getFullName(string SIP_Num)
        {
            /* MySQL寫法
            string selStr = "SELECT FullName FROM ccpsip.reg_user WHERE Status='Y' AND SIP_Num=?SIP_Num";
            MySqlParameter param1 = new MySqlParameter("?SIP_Num", SIP_Num);
            MySqlParameter[] cmdParams = new MySqlParameter[] { param1 };
            return Convert.ToString(MySqlHelper.ExecuteScalar(getCcpsipConnStr(), selStr, cmdParams));
            */

            OracleConnection connOracle = new OracleConnection(GetConnStr());
            OracleCommand cmd = new OracleCommand("SELECT FullName FROM reg_user WHERE Status='Y' AND SIP_Num=:SIP_Num", connOracle);
            cmd.Parameters.Add(":SIP_Num", OracleType.VarChar).Value = SIP_Num;
            connOracle.Open();

            string ReturnFullName = Convert.ToString(cmd.ExecuteScalar());
            connOracle.Dispose();

            return ReturnFullName;
        }

    }
}